/*******************************************************************/
-- RECREATE DATABASE
\c postgres
drop database if exists demo_tsearch;
create database demo_tsearch;
\c demo_tsearch postgres
\set ON_ERROR_STOP on

/*******************************************************************/
-- CREATE TABLES
drop table if exists book_xml, book_time_machine, book_heart_darkness;
create table book_xml(title text,content xml);
create table book_time_machine(content text, tsearch tsvector);
create table book_heart_darkness(content text, tsearch tsvector);

/*******************************************************************/
/* LOAD TABLES
- loading the tables assumes files are available in the data cluster
- the file to load need a little preediting i.e. remove the following:
  - <!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.1//EN' 'http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd'>
  -  xmlns="http://www.w3.org/1999/xhtml"
*/

insert into book_xml(title,content) values('The Time Machine',pg_read_file('file_the_time_machine.html')::xml);
insert into book_xml(title,content) values('Heart Of Darkness',pg_read_file('file_heart_of_darkness.html')::xml);

insert into book_time_machine(content)
select unnest(xpath('/html/body/p/text()',content))::text from book_xml where title like 'The Time Machine';

insert into book_heart_darkness(content)
select unnest(xpath('/html/body/p/text()',content))::text from book_xml where title like 'Heart Of Darkness';

update book_time_machine set tsearch=to_tsvector(content);
update book_heart_darkness set tsearch=to_tsvector(content);

create index time_mach_tsearch on book_time_machine using gin(tsearch);
create index hrt_drk_tsearch on book_heart_darkness using gin(tsearch);

analyze;
